<?php
/* Xenon MySQL Wrapper by Olivier St-Laurent
 * Version 1.2
 *
 *
 * Usage :
 *
 *  Connect with database
 *      new X_mysql('host', 'user', 'password', 'database'); // You do not need to run this if using this wrapper within XenonPHP
 *
 *  Create an instance of the Query wrapper, with the table name
 *      $users = new X_mysql_query('users');
 *
 *  Fetch all data from a table as an array (SELECT * FROM `users`)
 *      $users->fetchAll();
 *
 *    Same, using a Where clause
 *      $users->where("name = ?", $value)->fetchAll();
 *    or
 *      $users->where(...);
 *      $users->where(...)->orWhere(...);
 *      $users->fetchAll();
 *
 *  Fetch rows One by one as an array
 *      $users->fetchRow();
 *
 *  Insert data
 *      $users->insert(array('name'=>"Value", 'email'=>"Value"))->execute();
 *   or
 *      $users->insert(array('name'=>"Value", 'email'=>"Value"));
 *      $users->insert(array('name'=>"Value2", 'email'=>"Value2"));
 *      $users->execute();
 *
 *  Update Data
 *      $users->update(array('name'=>"Value"))->where("id=?", $id)->execute();
 *
 *  Delete Data
 *      $users->delete("id=?", 2)->execute(); // Deletes the row where id = 2
 *
 *  Get the query that would be executed before doing ->execute() (Useful for debugging)
 *      echo $users->query();
 *    or
 *      echo $users; // the __toString() method does the same
 *
 *  More complex Query (SELECT `name`,`email` FROM `users` WHERE (`id` = '3') AND (`active` = 1) ORDER BY `id` ASC LIMIT 1
 *      $users->select(array('name','email'))
 *            ->where('id=?', 3)
 *            ->where('active=1')
 *            ->order('id ASC')
 *            ->limit(1);
 *      $users->fetchRow();
 *
 *  Inner Join Example (SELECT `users`.*, `countries`.`name` AS 'country_name' FROM `users` INNER JOIN `countries` ON `countries`.`id` = `users`.`country`)
 *      $users->joinInner('countries', "countries.id=users.country", array('country_name'=>'name'))->fetchAll();
 *
 *  Using custom queries
 *      $users->query("UPDATE `table` SET.....")->execute();
 *
 *
 *  Table Instances examples
 *      $users = new X_mysql_table('users');
 *  Fetching one row as an editable object
 *      $user = $users->fetchRow();
 *      $user->name = "New Name";
 *      $user->save();
 *    Same, fetching one specific row with a specific ID in the primary key
 *      $user = $users->fetchRowId(5);
 *  Fetching All rows as an array of objects, go through each and edit them all, then save them all in one command
 *      $userArray = $users->fetchAll();
 *      foreach ($userArray as $user) {
 *          $user->name = "New Name For All";
 *      }
 *      $users->saveAll();
 *
 */

class X_mysql
{
    static $DropBeforeRebuild = false;
    static $RebuildTables = false;
    static $cacheFile = null;
    static $schemaFile = null;
    static $DefaultConnection = null;
    static $Login = array(
        'host'     => "localhost",
        'user'     => "",
        'password' => "",
        'database' => "",
        'charset'  => null,
    );
    static $defaultTableCreateSpecs = "ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci";
    public $host;
    public $user;
    public $password;
    public $database;
    public $flags;
    public $charset;
    public $link;
    public $connected = false;
    public $tables = array();

    public function __construct($host = null, $user = null, $password = null, $database = null, $charset = null)
    {
        if ($host === null) {
            $host = self::$Login;
        }
        if (is_array($host)) {
            $login = $host;
            $host = $login['host'];
            if ($user !== null) {
                $this->connectionID = $user;
                $user = null;
            }
            if (isset($login['user']))
                $user = $login['user'];
            if (isset($login['password']))
                $password = $login['password'];
            if (isset($login['database']))
                $database = $login['database'];
            if (isset($login['charset']))
                $charset = $login['charset'];
        }
        $this->host = $host;
        $this->user = $user;
        $this->password = $password;
        $this->database = $database;
        $this->charset = ($charset === null) ? 'UTF8' : $charset;
        if ($this->connect() && self::$DefaultConnection === null) {
            self::$DefaultConnection = $this->link;
        }
    }

    public function isConnected()
    {
        return $this->connected;
    }

    public function close()
    {
        $this->connected = false;
        if ($this->link) {
            mysqli_close($this->link);
        }
    }

    public function connect()
    {
        if (!empty($this->host) && !empty($this->user) && !empty($this->password)) {
            if (($this->link = mysqli_connect($this->host, $this->user, $this->password, $this->database))) {
                mysqli_set_charset($this->link, $this->charset);
                $this->connected = true;
                return $this->link;
            }
        }
        return false;
    }

    public function __toString()
    {
        return ($this->link) ? "Connected with resource " . $this->link : "NOT CONNECTED";
    }

    public function update($table, $values, $cond = null, $escapedVal = null, $escapedVal2 = null)
    {
        $db = new X_mysql_query($table, $this->link);
        $db->update($values);
        if ($cond !== null)
            $db->where($cond, $escapedVal, $escapedVal2);
        return $db->execute();
    }

    public function delete($table, $cond = null, $escapedVal = null, $escapedVal2 = null)
    {
        $db = new X_mysql_query($table, $this->link);
        if ($cond !== null)
            $db->where($cond, $escapedVal, $escapedVal2);
        return $db->delete()->execute();
    }

    public function lastInsertId()
    {
        return mysqli_insert_id($this->link);
    }

    public function insert($table, $values)
    {
        $db = new X_mysql_query($table, $this->link);
        return $db->insert($values)->execute();
    }

    public function select($select, $table, $where = null, $order = null, $limit = null)
    {
        $db = new X_mysql_query($table, $this->link);
        if ($where !== null)
            $db->where($where);
        if ($order !== null)
            $db->order($order);
        if ($limit !== null)
            $db->limit($limit);
        return $db->select()->fetchAll();
    }

    public function selectRow($select, $table, $where = null, $order = null)
    {
        $db = new X_mysql_query($table, $this->link);
        if ($where !== null)
            $db->where($where);
        if ($order !== null)
            $db->order($order);
        return $db->select()->fetchRow();
    }

    public function selectOne($field, $table, $where = null, $order = null, $start = 0)
    {
        $db = new X_mysql_query($table, $this->link);
        if ($where !== null)
            $db->where($where);
        if ($order !== null)
            $db->order($order);
        $db->limit($start, 1);
        return $db->select()->fetch($field);
    }

    public function selectRowById($table, $id, $field = 'id', $where = null)
    {
        $db = new X_mysql_query($table, $this->link);
        return $db->fetchRowId($id, $field, $where);
    }

    public function query($query)
    {
        $result = $this->link->query($query);
        if ($result === false) {
            X_mysql::error("Error in MySQL Query : " . mysqli_error($this->link) . ". Query : {" . $query . "}");
        }
        return $result;
    }

    public function newQuery($table)
    {
        return new X_mysql_query($table, $this->link);
    }

    public function truncate($table)
    {
        return $this->query("TRUNCATE TABLE `" . X_mysql_escape($table, $this->link) . "`;");
    }

    public function showTables($recursiveSchema = false)
    {
        $results = $this->query("SHOW TABLES;");
        $tables = array();
        while ($table = mysqli_fetch_array($results)) {
            if ($recursiveSchema) {
                $tables[$table[0]] = $this->showColumns($table[0]);
            } else {
                $tables[$table[0]] = $table[0];
            }
        }
        return $tables;
    }

    public function showColumns($table)
    {
        return $this->newQuery($table)->fetchColumns();
    }

    static function deleteSchemaFile()
    {
        if (is_file(self::$schemaFile))
            unlink(self::$schemaFile);
    }

    static function deleteCacheFile()
    {
        if (is_file(self::$cacheFile))
            unlink(self::$cacheFile);
    }

    static function storeInSchemaFile($query)
    {
        if (self::$schemaFile) {
            file_put_contents(self::$schemaFile, $query . "\n\n", FILE_APPEND);
            chmod(self::$schemaFile, 0777);
        }
    }

    static function storeInCacheFile($query)
    {
        if (self::$cacheFile) {
            file_put_contents(self::$cacheFile, $query . "\n\n", FILE_APPEND);
            chmod(self::$cacheFile, 0777);
        }
    }

    public function configureTable($table, $fields, $primaryKey = '', $specs = '', $prefix = null)
    {
        if ($prefix === true) {
            $prefix = $table . '_';
        }
        if ($prefix === null) {
            foreach ($fields as $name => $type) {
                if ($prefix === null) {
                    $prefix = $name;
                } else {
                    for ($i = strlen($prefix); $i >= 0; $i--) {
                        $prefix = substr($prefix, 0, $i);
                        if (preg_match("#^" . preg_quote($prefix) . "#", $name)) {
                            break;
                        }
                    }
                }
            }
        }
        $this->tables[$table] = array('primary' => $primaryKey, 'fields' => $fields, 'specs' => $specs, 'prefix' => $prefix);
        if (self::$RebuildTables) {
            $this->createTable($table, $fields, $primaryKey, $specs, $createQuery, $alterQuery);
            self::storeInSchemaFile($createQuery);
            if (!self::$DropBeforeRebuild) {
                self::storeInSchemaFile($alterQuery);
            }
            self::storeInCacheFile($createQuery);
            self::storeInCacheFile($alterQuery);
        }
    }

    public function createTable($tableName, $columns, $primary = "", $specs = "", &$createQuery = null, &$alterQuery = null)
    {
        if ($this->connected) {
            $tableName = X_mysql_escape($tableName, $this->link);
            if (is_array($columns)) {
                $cols = "";
                foreach ($columns as $col => $type) {
                    $cols .= (($cols != "") ? ",\n    " : "") . "`" . X_mysql_escape($col, $this->link) . "` " . $type;
                }
            } else
                $cols = $columns;
            if ($primary === "")
                $primary = (is_array($columns)) ? key($columns) : "";
            $primary = str_replace("`", "", $primary);
            if (!empty($primary) && !empty($cols)) {
                $primary = ",\n    PRIMARY KEY(`" . preg_replace("#[, ]+#", "`,`", $primary) . "`)";
            }
            if ($specs === "") {
                $specs = self::$defaultTableCreateSpecs;
            }
            $createQuery = "CREATE TABLE IF NOT EXISTS `" . $tableName . "` (\n    " . $cols . $primary . "\n) " . $specs . ";";
            $result = $this->query($createQuery);
            if (($results = $this->query("SHOW COLUMNS FROM `" . $tableName . "`"))) {
                $actualColumns = array();
                $dropColumns = "";
                $modifyColumns = "";
                while ($actualCol = mysqli_fetch_assoc($results)) {
                    $actualColumns[] = $actualCol['Field'];
                    if (array_key_exists($actualCol['Field'], $columns))
                        $modifyColumns .= (($modifyColumns != "") ? "," : "") . " MODIFY `" . X_mysql_escape($actualCol['Field'], $this->link) . "` " . preg_replace("#^(([^,^\(^\).]+((\([^\(^\).]*\))*[^,^\(^\).]*)*)+)(,.+)?$#is", "$1", $columns[$actualCol['Field']]);
                    else
                        $dropColumns .= (($dropColumns != "") ? "," : "") . " DROP `" . X_mysql_escape($actualCol['Field'], $this->link) . "`";
                }
                $newColumns = "";
                foreach ($columns as $col => $type) {
                    if (!in_array($col, $actualColumns))
                        $newColumns .= (($newColumns != "") ? "," : "") . " ADD `" . X_mysql_escape($col, $this->link) . "` " . preg_replace(array("#(FOREIGN KEY *\()#i", "#(INDEX *\()#i"), "ADD $1", $type);
                }
                $alterQuery = "ALTER TABLE `" . $tableName . "` " . ((!empty($newColumns)) ? " " . $newColumns : "") . ((!empty($modifyColumns)) ? ((!empty($newColumns)) ? "," : "") . " " . $modifyColumns : "") . ((!empty($dropColumns)) ? ((!empty($newColumns) || !empty($modifyColumns)) ? "," : "") . " " . $dropColumns : "") . ((!empty($specs)) ? ((!empty($newColumns) || !empty($modifyColumns) || !empty($dropColumns)) ? "," : "") . " " . $specs : "");
                $result = $this->query($alterQuery);
            }
            return $result;
        }
    }

    public static function error($message, $level = E_USER_NOTICE)
    {
        $backtrace = debug_backtrace();
        $caller = next($backtrace);
        while (basename($caller['file']) == basename(__FILE__)) {
            $caller = next($backtrace);
        }
        return trigger_error($message . ' in <b>' . $caller['function'] . '</b> called from <b>' . $caller['file'] . '</b> on line <b>' . $caller['line'] . "</b>                     \n<br />Error Handler : X_mysql::error", $level);
    }

}

function X_mysql_escape($text, $link = null)
{
    if ($link === null) {
        $link = X_mysql::$DefaultConnection;
    }
    return mysqli_real_escape_string($link, $text);
}

class X_mysql_query
{
    public $link;
    public $table;
    public $tableName;
    protected $select = null;
    protected $join = null;
    protected $update = null;
    protected $insert = null;
    protected $multiInsert = null;
    protected $insertKeys = null;
    protected $insertVals = null;
    protected $delete = false;
    protected $lowercase = false;
    protected $where;
    protected $order = null;
    protected $group = null;
    protected $having = null;
    protected $limit = null;
    protected $resultSet = null;
    protected $tableAS = '';
    protected $from = null;
    protected $fromAS = null;
    protected $query = null;
    private static $debugQueries = array();
    public static $debugFile = null;

    public function __construct($table = null, $link = null)
    {
        if ($table === null) {
            $table = $this->table;
        }
        if ($link === null) {
            $link = X_mysql::$DefaultConnection;
        }
        if ($link instanceof X_mysql) {
            $this->link = $link->link;
        } else {
            $this->link = $link;
        }
        if (!$this->link) {
            throw new Exception("Not Connected to Database.");
            exit;
        }
        if (($pos = stripos($table, " as "))) {
            $this->tableAS = " AS '" . X_mysql_escape(preg_replace("#^'(.+)'$#i", "$1", substr($table, $pos + 4)), $this->link) . "'";
            $table = substr($table, 0, $pos);
        }
        $this->tableName = $table;
        $this->table = $this->tableName($table);
        $this->where = new X_mysql_whereCondition($this->link);
    }

    public static function emptyDebugFile()
    {
        if (X_mysql_query::$debugFile)
            file_put_contents(X_mysql_query::$debugFile, "");
    }

    protected static function debugQuery($query)
    {
        self::$debugQueries[] = $query;
        if (self::$debugFile !== null) {
            file_put_contents(self::$debugFile, $query . "\n\n", FILE_APPEND);
        }
    }

    public static function getDebugQueries()
    {
        return implode("\n\n", self::$debugQueries);
    }

    public static function getLastDebugQuery()
    {
        return end(self::$debugQueries);
    }

    public static function getDebugQueriesAndClear()
    {
        $queries = self::getDebugQueries();
        self::$debugQueries = array();
        return $queries;
    }

    public function subQuery($select = null, $fromTable = null)
    {
        if ($fromTable === null) {
            $fromTable = $this->table;
        }
        $subQuery = new X_mysql_query($fromTable, $this->link);
        if ($select !== null) {
            $subQuery->select($select);
        }
        return $subQuery;
    }

    public static function val($val, $link)
    {
        if ($val instanceof X_mysql_query) {
            return "(" . $val->query() . ")";
        }
        if ($val instanceof X_mysql_expr) {
            return $val;
        }
        return "'" . X_mysql_escape($val, $link) . "'";
    }

    protected function getVal($val)
    {
        return self::val($val, $this->link);
    }

    protected function tableName($table)
    {
        $table = str_replace("`", "", $table);
        if (($pos = stripos($table, " as "))) {
            $table = substr($table, 0, $pos);
        }
        if (strpos($table, ".") !== false) {
            $table = explode(".", $table, 2);
            return "`" . X_mysql_escape($table[0], $this->link) . "`.`" . X_mysql_escape($table[1], $this->link) . "`";
        } else {
            return "`" . X_mysql_escape($table, $this->link) . "`";
        }
    }

    protected function keyName($key)
    {
        return "`" . X_mysql_escape(str_replace("`", "", $key), $this->link) . "`";
    }

    protected function fieldName($field, $table = null)
    {
        if ($field === "*")
            return $field;
        if ($table === null)
            $table = $this->tableName;
        $field = trim(str_replace("`", "", $field));
        $expr = null;
        if (preg_match("#([a-z_]+)\(([a-z0-9_\.\*]+)\)#i", $field, $matches)) {
            $expr = $matches[1];
            $field = $matches[2];
        }
        $as = "";
        if (($pos = stripos($field, " as "))) {
            $as = " AS '" . X_mysql_escape(preg_replace("#^'(.+)'$#i", "$1", substr($field, $pos + 4)), $this->link) . "'";
            $field = substr($field, 0, $pos);
        }
        if (strpos($field, ".") !== false) {
            $field = explode(".", $field, 3);
            if (count($field) == 3)
                $field = "`" . X_mysql_escape($field[0], $this->link) . "`.`" . X_mysql_escape($field[1], $this->link) . "`." . (($field[2] == "*") ? $field[2] : "`" . X_mysql_escape($field[2], $this->link) . "`");
            else
                $field = "`" . X_mysql_escape($field[0], $this->link) . "`." . (($field[1] == "*") ? $field[1] : "`" . X_mysql_escape($field[1], $this->link) . "`");
        } else {
            $field = $this->tableName($table) . ".`" . X_mysql_escape($field, $this->link) . "`";
        }
        return ($expr ? $expr . '(' : '') . $field . ($expr ? ')' : '') . $as;
    }

    public function select($select = "*", $defaultTable = null)
    {
        $this->query = null;
        if ($defaultTable === null)
            $defaultTable = $this->tableName;
        if (is_array($select)) {
            foreach ($select as $key => $val) {
                if (is_int($key)) {
                    $this->select .= (($this->select === null) ? '' : ', ') . $this->fieldName($val, $defaultTable);
                } else {
                    $this->select .= (($this->select === null) ? '' : ', ') . $this->getVal($val) . " as '" . X_mysql_escape($key, $this->link) . "'";
                }
            }
        } else {
            if ($select instanceof X_mysql_expr) {
                $this->select .= (($this->select === null) ? '' : ', ') . $select;
            } else {
                if ($select == "*") {
                    if ($this->select == null) {
                        $this->select = "*";
                    }
                } else {
                    foreach (preg_split("# *, *#i", $select) as $s) {
                        $this->select .= (($this->select === null) ? '' : ', ') . $this->fieldName($s, $defaultTable);
                    }
                }
            }
        }
        return $this;
    }

    public function from($table, $as = null)
    {
        $this->query = null;
        if ($table instanceof X_mysql_expr) {
            $this->from = $table;
        } else if ($table instanceof X_mysql_query) {
            $this->from = "(" . $table->query() . ")";
            if ($as === null)
                $as = str_replace('`', '', $table->tableName) . '_tmp';
        } else {
            $table = str_replace("`", "", $table);
            if (($pos = stripos($table, " as "))) {
                $this->fromAS = " AS " . X_mysql_escape(preg_replace("#^'(.+)'$#i", "$1", substr($table, $pos + 4)), $this->link);
                $this->from = substr($table, 0, $pos);
            }
        }
        if ($as !== null) {
            $this->fromAS = " AS " . X_mysql_escape($as, $this->link);
        }
        return $this;
    }

    public function join($table, $cond = null, $select = null, $type = "")
    {
        $this->query = null;
        if ($cond === null && $type === "") {
            $type = "NATURAL";
        } else {
            if (preg_match("#^[a-z0-9_]+$#i", $cond)) {
                $cond = '`' . X_mysql_escape($table, $this->link) . '`.`' . X_mysql_escape($cond, $this->link) . '`=' . $this->table . '.`' . X_mysql_escape($cond, $this->link) . '`';
            }
        }
        $as = '';
        if (($pos = stripos($table, " as "))) {
            $as = " AS '" . X_mysql_escape(preg_replace("#^'(.+)'$#i", "$1", substr($table, $pos + 4)), $this->link) . "'";
            $table = substr($table, 0, $pos);
        }
        if ($select !== null)
            $this->select($select, $table);
        $this->join .= " " . $type . " JOIN " . $this->tableName($table) . $as . (($cond !== null) ? " ON " . $cond : "");
        return $this;
    }

    public function joinInner($table, $cond, $select = null)
    {
        return $this->join($table, $cond, $select, "INNER");
    }

    public function joinLeft($table, $cond, $select = null)
    {
        return $this->join($table, $cond, $select, "LEFT");
    }

    public function joinRight($table, $cond, $select = null)
    {
        return $this->join($table, $cond, $select, "RIGHT");
    }

    public function innerJoin($table, $cond, $select = null)
    {
        return $this->join($table, $cond, $select, "INNER");
    }

    public function leftJoin($table, $cond, $select = null)
    {
        return $this->join($table, $cond, $select, "LEFT");
    }

    public function rightJoin($table, $cond, $select = null)
    {
        return $this->join($table, $cond, $select, "RIGHT");
    }

    public function lowerCase($lowercase = true)
    {
        $this->lowercase = $lowercase;
        return $this;
    }

    public function lastInsertId()
    {
        return mysqli_insert_id($this->link);
    }

    public function insert($values = array())
    {
        $this->query = null;
        $this->insert = true;
        if ($this->insertVals !== null) {
            $this->multiInsert = true;
            if (!is_array($this->insertVals))
                $this->insertVals = array($this->insertVals);
        }
        if (is_array($values)) {
            if (!is_array(current($values))) {
                $values = array($values);
            } else
                $this->multiInsert = true;
            foreach ($values as $vals) {
                $i = 0;
                $insertVals = null;
                foreach ($vals as $key => $val) {
                    if ($key !== $i++ && $this->insertVals === null) {
                        $this->insertKeys .= (($this->insertKeys === null) ? "" : ", ") . $this->keyName($key);
                    }
                    $insertVals .= (($insertVals === null) ? "" : ", ") . $this->getVal($val);
                }
                if ($this->multiInsert) {
                    $this->insertVals[] = $insertVals;
                } else {
                    $this->insertVals = $insertVals;
                }
            }
        } else {
            if ($this->insertVals === null) {
                $this->insertVals = $values;
            } else {
                $this->insertVals[] = $values;
            }
        }
        return $this;
    }

    public function delete($id = null)
    {
        $this->query = null;
        $this->delete = true;
        if ($id !== null && is_numeric($id)) {
            $idField = ($this->idField ? $this->idField : 'id');
            $this->where($idField, $id);
        }
        return $this;
    }

    /*
     * $order= array('id'=>'ASC','name'=>'DESC')
     */

    public function order($order)
    {
        $this->query = null;
        if (is_array($order)) {
            $i = 0;
            foreach ($order as $key => $val) {
                if ($key === $i++) {
                    $key = $val;
                    $val = "ASC";
                }
                if (!preg_match("#^[\w\.]$#i", $key)) {
                    $this->order .= (($this->order === null) ? "" : ", ") . $key . " " . $val;
                } else {
                    $this->order .= (($this->order === null) ? "" : ", ") . $this->fieldName($key) . " " . $val;
                }
            }
        } else {
            if (!empty($order))
                $this->order .= (($this->order === null) ? "" : ", ") . X_mysql_escape($order, $this->link);
        }
        return $this;
    }

    public function group($group)
    {
        $this->query = null;
        $this->group = $this->fieldName($group);
        return $this;
    }

    public function having($condition)
    {
        $this->query = null;
        $this->having = $condition;
        return $this;
    }

    public function limit($start, $length = null)
    {
        $this->query = null;
        if ($length === null) {
            $length = (int) $start;
            $start = 0;
        }
        $this->limit = X_mysql_escape((int) $start . "," . (int) $length, $this->link);
        return $this;
    }

    public function update($values)
    {
        $this->query = null;
        if (is_array($values)) {
            foreach ($values as $key => $val) {
                $this->update .= (($this->update) ? "," : "") . "`" . X_mysql_escape($key, $this->link) . "`=" . $this->getVal($val);
            }
        } else {
            $this->update = $values;
        }
        return $this;
    }

    public function where($cond, $escapedVal = null, $escapedVal2 = null)
    {
        $this->query = null;
        $this->where->where($cond, $escapedVal, $escapedVal2);
        return $this;
    }

    public function subWhere()
    {
        return $this->where->subWhere();
    }

    public function orWhere($cond, $escapedVal = null, $escapedVal2 = null)
    {
        $this->query = null;
        $this->where->orWhere($cond, $escapedVal, $escapedVal2);
        return $this;
    }

    public function fetchColumns()
    {
        $results = $this->link->query("SHOW COLUMNS FROM " . $this->table . ";");
        $columns = array();
        if ($results) {
            while ($row = mysqli_fetch_assoc($results)) {
                $columns[current($row)] = $row;
            }
        }
        return $columns;
    }

    public function count($field = "*")
    {
        if ($this->select === null)
            $this->select(new X_mysql_expr("COUNT(" . $this->fieldName($field) . ") as 'X_count'"));
        $this->executeQuery();
        if ($this->resultSet) {
            $row = mysqli_fetch_assoc($this->resultSet);
            return $row['X_count'];
        }
        return false;
    }

    public function fetchAll($associative = false)
    {
        $this->resultSet = null;
        if ($this->select === null)
            $this->select();
        $this->executeQuery();
        $resultArray = array();
        if ($this->resultSet) {
            if ($associative !== false) {
                if ($associative === true) {
                    while ($result = mysqli_fetch_assoc($this->resultSet))
                        $resultArray[current($result)] = (($this->lowercase) ? array_change_key_case($result, CASE_LOWER) : $result);
                } else {
                    while ($result = mysqli_fetch_assoc($this->resultSet))
                        $resultArray[$result[$associative]] = (($this->lowercase) ? array_change_key_case($result, CASE_LOWER) : $result);
                }
            } else {
                while ($result = mysqli_fetch_assoc($this->resultSet))
                    $resultArray[] = (($this->lowercase) ? array_change_key_case($result, CASE_LOWER) : $result);
            }
            $this->reset(true, true);
            return $resultArray;
        }
        return false;
    }

    public function fetchRow()
    {
        if ($this->select === null)
            $this->select();
        $this->executeQuery();
        if ($this->resultSet) {
            $row = mysqli_fetch_assoc($this->resultSet);
            if ($this->lowercase && is_array($row))
                return array_change_key_case($row, CASE_LOWER);
            return $row;
        }
        return false;
    }

    public function fetchRowId($id, $field = 'id', $where = null, $cond = null, $escapeVal = null, $escapedVal2 = null)
    {
        $this->resultSet = null;
        if ($this->select === null)
            $this->select();
        $this->where($this->fieldName($field) . " = ?", $id);
        if ($where !== null)
            $this->where($where, $cond, $escapeVal, $escapedVal2);
        $this->executeQuery();
        if ($this->resultSet) {
            $row = mysqli_fetch_assoc($this->resultSet);
            $this->resultSet = null;
            if ($this->lowercase && is_array($row))
                return array_change_key_case($row, CASE_LOWER);
            return $row;
        }
        return false;
    }

    public function fetch($field)
    {
        if ($this->select === null)
            $this->select($field);
        $this->executeQuery();
        if (($results = mysqli_fetch_array($this->resultSet))) {
            $row = array_change_key_case($results, CASE_LOWER);
            return $row[strtolower($field)];
        }
        return false;
    }

    public function fetchRowToObject($classname = null, $table = null, $idField = "id", $link = null)
    {
        if ($table === null) {
            $table = $this->table;
        }
        $this->resultSet = null;
        if ($this->select === null)
            $this->select();
        $this->executeQuery();
        if ($this->resultSet) {
            if (empty($classname)) {
                return mysqli_fetch_object($this->resultSet);
            } else {
                if (($row = mysqli_fetch_assoc($this->resultSet))) {
                    return new $classname($row, $table, $idField, $link);
                }
            }
        }
        return false;
    }

    public function fetchAllToObjects($classname = null, $table = null, $idField = "id", $link = null)
    {
        if ($table === null) {
            $table = $this->table;
        }
        $this->resultSet = null;
        if ($this->select === null)
            $this->select();
        $this->executeQuery();
        $resultArray = array();
        if ($this->resultSet) {
            if (empty($classname)) {
                while ($result = mysqli_fetch_object($this->resultSet))
                    $resultArray[$result->$idField] = $result;
            } else {
                while (($row = mysqli_fetch_assoc($this->resultSet)) && $result = new $classname($row, $table, $idField, $link))
                    $resultArray[$result->$idField] = $result;
            }
            $this->reset(true, true);
            return $resultArray;
        }
        return false;
    }

    public function reset($resetAction = false, $resetWhere = false)
    {
        $this->resultSet = null;
        $this->query = null;
        if ($resetAction) {
            $this->select = null;
            $this->update = null;
            $this->insert = null;
            $this->multiInsert = null;
            $this->delete = false;
            $this->order = null;
            $this->group = null;
            $this->limit = null;
            $this->join = null;
            $this->insertKeys = null;
            $this->insertVals = null;
            $this->from = null;
            $this->fromAS = null;
        }
        if ($resetWhere) {
            $this->where = new X_mysql_whereCondition($this->link);
            $this->having = null;
        }
        return $this;
    }

    public function execute()
    {
        $result = $this->executeQuery();
        $this->reset(true, true);
        return $result;
    }

    public function query($query = null)
    {
        if ($query !== null)
            return $this->query = $query;
        return $this->query = $this->buildStatement();
    }

    protected function executeQuery()
    {
        if ($this->resultSet === null && ($query = $this->query) !== null || ($query = $this->buildStatement())) {
            X_mysql_query::debugQuery($query);
            return $this->resultSet = $this->link->query($query) or die(mysqli_error($this->link) . " : " . $query);
        }
    }

    protected function buildStatement()
    {
        $table = $this->table;
        $query = null;
        if ($this->multiInsert) {
            $values = "";
            foreach ($this->insertVals as $insertVals) {
                $values .= (($values != "") ? ',' : '') . "(" . $insertVals . ")";
            }
            $query = "INSERT INTO " . $table . (($this->insertKeys === null) ? "" : "(" . $this->insertKeys . ")") . (!empty($values) ? " VALUES" . $values : 'VALUES()');
        } else {
            if ($this->insert) {
                $query = "INSERT INTO " . $table . (($this->insertKeys === null) ? "" : "(" . $this->insertKeys . ")") . " VALUES(" . $this->insertVals . ")";
            } else {

                if ($this->select !== null || ($this->select = "*")) {
                    $tableAS = $this->tableAS;
                    if ($this->from !== null)
                        $table = $this->from;
                    if ($this->fromAS !== null)
                        $tableAS = $this->fromAS;
                    $query = "SELECT " . $this->select . " FROM " . $table . $tableAS;
                }
                if ($this->update !== null)
                    $query = "UPDATE " . $table . " SET " . $this->update;
                if ($this->delete)
                    $query = "DELETE FROM " . $table;

                if ($query != null) {
                    if ($this->join !== null)
                        $query .= $this->join;
                    if ($this->where != '(1)')
                        $query .= " WHERE " . $this->where;
                    if ($this->group !== null)
                        $query .= " GROUP BY " . $this->group;
                    if ($this->having !== null)
                        $query .= " HAVING " . $this->having;
                    if ($this->order !== null)
                        $query .= " ORDER BY " . $this->order;
                    if ($this->limit !== null)
                        $query .= " LIMIT " . $this->limit;
                }
            }
        }
        return $query;
    }

    public function __toString()
    {
        return (string) $this->buildStatement();
    }

}

class X_mysql_expr
{
    protected $expr;

    public function __construct($expr, $as = null)
    {
        if ($expr instanceof X_mysql_query) {
            $this->expr = "(" . $expr->query() . ")" . ($as ? " AS '" . $as . "'" : '');
        } else {
            $this->expr = $expr . ($as ? " AS '" . $as . "'" : '');
        }
    }

    public function __toString()
    {
        return $this->expr;
    }

}

class X_mysql_whereCondition
{
    public $condition = "";
    public $link;

    public function __construct($link = null, $cond = null, $escapeVal = null, $escapedVal2 = null)
    {
        $this->link = $link;
        if ($cond !== null) {
            $this->where($cond, $escapeVal, $escapedVal2);
        }
    }

    protected function getVal($val)
    {
        return X_mysql_query::val($val, $this->link);
    }

    public function where($cond, $escapedVal = null, $escapedVal2 = null)
    {
        return $this->addWhereClause($cond, $escapedVal, $escapedVal2);
    }

    public function subWhere()
    {
        return new X_mysql_whereCondition($this->link);
    }

    public function orWhere($cond, $escapedVal = null, $escapedVal2 = null)
    {
        return $this->addWhereClause($cond, $escapedVal, $escapedVal2, true);
    }

    public function andWhere($cond, $escapedVal = null, $escapedVal2 = null)
    {
        return $this->addWhereClause($cond, $escapedVal, $escapedVal2);
    }

    protected function addWhereClause($cond, $escapedVal = null, $escapedVal2 = null, $or = false)
    {
        if ($escapedVal !== null && preg_match("#^[a-z0-9_\.]+$#i", $cond)) {
            $cond = "`" . str_replace(".", "`.`", $cond) . "` = ?";
        }
        $maxLoop = 1;
        $initLoop = 0;
        do {
            if ($escapedVal !== null) {
                if (is_array($escapedVal)) {
                    $vals = "";
                    foreach ($escapedVal as $val) {
                        $vals .= (($vals == "") ? '' : ',') . $this->getVal($val);
                    }
                    if (empty($vals)) {
                        $vals = "0";
                    }
                    $cond = preg_replace("#(\('?\?'?\))|('\?')|(\?)#", "(" . $vals . ")", $cond, 1);
                } else {
                    $cond = preg_replace("#(\('?\?'?\))|('\?')|(\?)#", $this->getVal($escapedVal), $cond, 1);
                }
            }
            $escapedVal = $escapedVal2;
        } while ($initLoop++ < $maxLoop);
        $this->condition .= (($this->condition == "") ? "" : (($or) ? " OR " : " AND ")) . $cond;
        return $this;
    }

    public function __toString()
    {
        if ($this->condition == "") {
            return "(1)";
        } else {
            return "(" . $this->condition . ")";
        }
    }

}

class X_mysql_table extends X_mysql_query
{
    public $idField = 'id';
    protected $data;
    protected $rowClassName = 'X_mysql_row';

    public function fetchAll($idField = null)
    {
        if ($idField !== null) {
            $this->idField = $idField;
        }
        $className = get_called_class() . 'Row';
        if (class_exists($className)) {
            $this->data = $this->fetchAllToObjects($className, $this->table, $this->idField, $this->link);
        } else if (class_exists($this->rowClassName)) {
            $this->data = $this->fetchAllToObjects($this->rowClassName, $this->table, $this->idField, $this->link);
        } else {
            $this->data = parent::fetchAll(true);
        }
        return $this->data;
    }

    public function fetchRow($id = null, $idField = null)
    {
        $className = (get_called_class() == 'X_mysql_table') ? 'X_mysql_row' : get_called_class() . 'Row';
        if (!class_exists($className))
            $className = 'X_mysql_row';
        $query = $this->select();
        if ($id !== null) {
            $query->where(($idField ? $idField : $this->idField) . "=?", $id);
        }
        return $query->fetchRowToObject($className, $this->table, $this->idField, $this->link);
    }

    public function toArray($idField = null)
    {
        if ($idField === null) {
            $idField = $this->idField;
        }
        $array = array();
        foreach ($this->data as $object) {
            $array[$object->$idField] = $object->toArray();
        }
        return $array;
    }

    public function saveAll()
    {
        foreach ($this->data as $object) {
            $object->save();
        }
    }

}

class X_mysql_row
{
    protected $link;
    protected $table;
    protected $idField = "id";
    protected $query;
    protected $data = array();
    protected $modifiedData = array();

    public function __construct($data, $table, $idField = "id", $link = null)
    {
        $this->data = $data;
        if ($link === null)
            $link = X_mysql::$DefaultConnection;
        $this->link = $link;
        $this->table = $table;
        $this->idField = $idField;
        $this->query = new X_mysql_query($this->table, $this->link);
    }

    public function fetch()
    {
        $this->data = $this->query->fetchRowId($this->$this->idField, $this->idField);
    }

    public function save()
    {
        if (!empty($this->modifiedData)) {
            if ($this->query->update($this->modifiedData)->where($this->idField . "=?", $this->data[$this->idField])->execute()) {
                $this->modifiedData = array();
                return true;
            }
            X_mysql::error("Save failed, no data has been modified. " . mysqli_error($this->link) . " {" . $this->query . "} ");
            return false;
        }
        return true;
    }

    public function findColumnName($field, $triggerNotice = true, $findMethodName = false)
    {
        $table = str_replace("`", "", $this->table);
        $fields = array(
            $field,
        );
        if (preg_match("#^" . preg_quote($table) . "_#", $field)) {
            $fields[] = preg_replace("#^(" . preg_quote($table) . ")?_(.*$)#", "$2", $field);
        } else {
            $fields[] = $table . "_" . $field;
            $fields[] = substr($table, 0, 1) . "_" . $field;
        }
        if ($findMethodName === false) {
            foreach ($fields as $f) {
                if (array_key_exists($f, $this->data))
                    return $f;
            }
            foreach ($this->data as $key => $val) {
                if (preg_match("#_" . preg_quote($field) . "$#i", $key)) {
                    return $key;
                }
            }
            if ($triggerNotice)
                X_mysql::error("Column '" . implode("' or '", $fields) . "' or any mathing *_field does not exist");
        } else {
            foreach ($fields as $key => $f) {
                $fields[$key] = $findMethodName . $f;
                if (method_exists(get_called_class(), $fields[$key]))
                    return $fields[$key];
            }
            if ($triggerNotice)
                X_mysql::error("Method '" . implode("' or '", $fields) . "' does not exist");
        }
        return false;
    }

    public function __set($name, $value)
    {
        $method = $this->findColumnName($name, false, '_set_');
        if ($method) {
            $value = $this->$method($value);
        }
        $fieldName = $this->findColumnName($name, $method == false);
        if ($fieldName) {
            $this->data[$fieldName] = $value;
            $this->modifiedData[$fieldName] = $value;
        }
    }

    public function __get($name)
    {
        $method = $this->findColumnName($name, false, '_get_');
        $fieldName = $this->findColumnName($name, $method == false);
        if ($fieldName) {
            if ($method) {
                return $this->$method($this->data[$fieldName]);
            }
            return $this->data[$fieldName];
        } else if ($method) {
            return $this->$method();
        }
    }

    public function toArray()
    {
        return $this->data;
    }

    public function __toString()
    {
        return $this->table . " #" . $this->id;
    }

}
